package com.picvik.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

import java.util.ArrayList;
import java.util.Date;

import com.picvik.util.MyLog;

public class DiscussionUtil {
	// DB related private variables
	private Connection connection = null;
    private PreparedStatement preparedStatement = null;
    private ResultSet resultSet = null;
    
    // Util Methods
    
    // Insertion Methods.
    
    // Add new discussion to the table.
    public Integer insertDiscussion(DiscussionModel discussion){
    	
    	Integer recentid = -1;
    	
    	if(discussion == null){
    		return -1;
    	}
    	
    	// Insert the data.
    	try {
    		DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
    		Date date = new Date();
    		//query string 
    		String qstring = "INSERT INTO picvik_discussion (title, description, owner_id, lastmodified) " +
    				"VALUES('" + discussion.getTitle() +  "', '" + discussion.getDescription() + "', " + discussion.getOwner_id() +", '"+ dateFormat.format(date) +  "' );";
    		System.out.println(qstring);
    		MyLog.log(qstring);
    		
    		connection = com.picvik.util.MySqlConnection.getInstance().getConnection();
            
    		preparedStatement = connection.prepareStatement(qstring);
            
            preparedStatement.executeUpdate(qstring);
            
            String getLastId = "SELECT LAST_INSERT_ID() FROM picvik_discussion;";
            
            preparedStatement = connection.prepareStatement(getLastId);
            resultSet = preparedStatement.executeQuery();
            resultSet.next();
            recentid = resultSet.getInt(1);
            preparedStatement.close();
            connection.close();
		
    	} catch (Exception e) {
    		connection = null;
			preparedStatement = null;
			resultSet = null;
    		e.printStackTrace();
    	}		
    	return recentid;
    }
    
    // Updates the discussion's last modified time, given its discussion id. 
    // Useful when a new comment is added the last modified should be updated.
    // This should be done without fail, because its heart for our recommendation engine.
    public void updateDiscussionLastModifiedTime(int discussionId){
    	    	
    	// Insert the data.
    	try {
    		DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
    		Date date = new Date();
	        
    		//query string 
    		String qstring = "UPDATE picvik_discussion SET lastmodified = '" + dateFormat.format(date) + 
    				"' WHERE id = " + discussionId +";";
    		
    		MyLog.log(qstring);
    		
    		connection = com.picvik.util.MySqlConnection.getInstance().getConnection();
            
    		preparedStatement = connection.prepareStatement(qstring);
            
            preparedStatement.executeUpdate(qstring);
            
            preparedStatement.close();
            connection.close();
		
    	} catch (Exception e) {
    		connection = null;
			preparedStatement = null;
			resultSet = null;
    		e.printStackTrace();
    	}		
    }
    
    // Retrieval methods
    
    // Get most recent 50 discussion going on
    public ArrayList<DiscussionModel> getRecentFiftyDiscussion(){
    	
    	ArrayList<DiscussionModel> latestFiftyDiscussion = new ArrayList<DiscussionModel>();
    	
    	try{
			
			// Most recent comment at end. so ASC
			String getdiscussionCommentQuery = "SELECT * FROM picvik_discussion " +
					" ORDER BY lastmodified DESC LIMIT 50;";
			
			MyLog.log(getdiscussionCommentQuery);
			
			connection = com.picvik.util.MySqlConnection.getInstance().getConnection();
			
			preparedStatement = connection.prepareStatement(getdiscussionCommentQuery);
			resultSet = preparedStatement.executeQuery();
			
			while(resultSet.next()){
				DiscussionModel discussionModel = new DiscussionModel();
			
				discussionModel.setId(resultSet.getInt("id"));
				discussionModel.setTitle(resultSet.getString("title"));
				discussionModel.setDescription(resultSet.getString("description"));
				discussionModel.setOwner_id(resultSet.getInt("owner_id"));
				discussionModel.setCreatedtime(resultSet.getTimestamp("createdtime"));
				discussionModel.setLastmodified(resultSet.getTimestamp("lastmodified"));
				
				discussionModel.setOwner_name(UserModel.getUserNameFromID(resultSet.getInt("owner_id")));
				
				latestFiftyDiscussion.add(discussionModel);
			}	
			
			connection.close();
			preparedStatement.close();
			resultSet.close();
				
			}catch(Exception ex){
				connection = null;
				preparedStatement = null;
				resultSet = null;
				
				ex.printStackTrace();
			}		
    	
    	return latestFiftyDiscussion;
    }
    
    // Get all discussions having tags like passed in parameter.
    public ArrayList<DiscussionModel> getDiscussionsWithTags(String[] tags){
    	ArrayList<DiscussionModel> discussionList = new ArrayList<DiscussionModel>();
    	
    	try{
    		connection = com.picvik.util.MySqlConnection.getInstance().getConnection();
    		
			for(String tag : tags){
				
				
				// Most recent comment at end. so ASC
				String getdiscussionCommentQuery = "SELECT * FROM picvik_discussion " +
						"WHERE tag =  '" + tag +"' ORDER BY lastmodified DESC;";
				
				MyLog.log(getdiscussionCommentQuery);
				
				preparedStatement = connection.prepareStatement(getdiscussionCommentQuery);
				resultSet = preparedStatement.executeQuery();
				
				while(resultSet.next()){
					DiscussionModel discussionModel = new DiscussionModel();
				
					discussionModel.setId(resultSet.getInt("id"));
					discussionModel.setTitle(resultSet.getString("title"));
					discussionModel.setDescription(resultSet.getString("description"));
					discussionModel.setOwner_id(resultSet.getInt("owner_id"));
					discussionModel.setCreatedtime(resultSet.getTimestamp("createdtime"));
					discussionModel.setLastmodified(resultSet.getTimestamp("lastmodified"));
					
					discussionModel.setOwner_name(UserModel.getUserNameFromID(resultSet.getInt("owner_id")));
					
					discussionList.add(discussionModel);
				}	
			}
			connection.close();
			preparedStatement.close();
			resultSet.close();
				
			}catch(Exception ex){
				connection = null;
				preparedStatement = null;
				resultSet = null;
				
				ex.printStackTrace();
			}
    	
    	return discussionList;    	
    }
    
    // Get all discussions by a given user.
    public ArrayList<DiscussionModel> getAllDiscussionsByUser(int ownerid){
    
    	ArrayList<DiscussionModel> discussionList = new ArrayList<DiscussionModel>();
    	
    	try{
			
			// Most recent comment at end. so ASC
			String getdiscussionCommentQuery = "SELECT * FROM picvik_discussion " +
					"WHERE ownerid = " + ownerid+ " ORDER BY lastmodified DESC;";
			
			MyLog.log(getdiscussionCommentQuery);
			
			connection = com.picvik.util.MySqlConnection.getInstance().getConnection();
			
			preparedStatement = connection.prepareStatement(getdiscussionCommentQuery);
			resultSet = preparedStatement.executeQuery();
			
			while(resultSet.next()){
				DiscussionModel discussionModel = new DiscussionModel();
			
				discussionModel.setId(resultSet.getInt("id"));
				discussionModel.setTitle(resultSet.getString("title"));
				discussionModel.setDescription(resultSet.getString("description"));
				discussionModel.setOwner_id(resultSet.getInt("owner_id"));
				discussionModel.setCreatedtime(resultSet.getTimestamp("createdtime"));
				discussionModel.setLastmodified(resultSet.getTimestamp("lastmodified"));
				
				discussionModel.setOwner_name(UserModel.getUserNameFromID(resultSet.getInt("owner_id")));
				
				discussionList.add(discussionModel);
			}	
			
			connection.close();
			preparedStatement.close();
			resultSet.close();
				
			}catch(Exception ex){
				connection = null;
				preparedStatement = null;
				resultSet = null;
				
				ex.printStackTrace();
			}		
    	return discussionList;
    }
    
    // Get all discussion with title similar to titles passed as parameter.
    public ArrayList<DiscussionModel> getDiscussionWithTitles(String[] titles){
    	ArrayList<DiscussionModel> discussionList = new ArrayList<DiscussionModel>();
    	
    	try{
    		connection = com.picvik.util.MySqlConnection.getInstance().getConnection();
    		
			for(String title : titles){
				// Most recent comment at end. so ASC
				String getdiscussionCommentQuery = "SELECT * FROM picvik_discussion " +
						"WHERE title =  '" + title +"' ORDER BY lastmodified DESC;";
				
				MyLog.log(getdiscussionCommentQuery);
				
				preparedStatement = connection.prepareStatement(getdiscussionCommentQuery);
				resultSet = preparedStatement.executeQuery();
				
				while(resultSet.next()){
					DiscussionModel discussionModel = new DiscussionModel();
				
					discussionModel.setId(resultSet.getInt("id"));
					discussionModel.setTitle(resultSet.getString("title"));
					discussionModel.setDescription(resultSet.getString("description"));
					discussionModel.setOwner_id(resultSet.getInt("owner_id"));
					discussionModel.setCreatedtime(resultSet.getTimestamp("createdtime"));
					discussionModel.setLastmodified(resultSet.getTimestamp("lastmodified"));
					
					discussionModel.setOwner_name(UserModel.getUserNameFromID(resultSet.getInt("owner_id")));
					
					discussionList.add(discussionModel);
				}	
			}
			connection.close();
			preparedStatement.close();
			resultSet.close();
				
			}catch(Exception ex){
				connection = null;
				preparedStatement = null;
				resultSet = null;
				
				ex.printStackTrace();
			}
    	return discussionList;
    }    
    
 // Get all discussion thread by a given id.
    public DiscussionModel getDiscussionThreadByID(int id){
    
    	DiscussionModel discussionModel = new DiscussionModel();
    	
    	try{
			
			// Most recent comment at end. so ASC
			String qstring = "SELECT * FROM picvik_discussion " +
					"WHERE id = " + id+ ";";
			
			MyLog.log(qstring);
			
			connection = com.picvik.util.MySqlConnection.getInstance().getConnection();
			
			preparedStatement = connection.prepareStatement(qstring);
			resultSet = preparedStatement.executeQuery();
			
			if(resultSet.next()){
				discussionModel.setId(resultSet.getInt("id"));
				discussionModel.setTitle(resultSet.getString("title"));
				discussionModel.setDescription(resultSet.getString("description"));
				discussionModel.setOwner_id(resultSet.getInt("owner_id"));
				discussionModel.setCreatedtime(resultSet.getTimestamp("createdtime"));
				discussionModel.setLastmodified(resultSet.getTimestamp("lastmodified"));
				discussionModel.setOwner_name(UserModel.getUserNameFromID(resultSet.getInt("owner_id")));
			}	
			
			connection.close();
			preparedStatement.close();
			resultSet.close();
				
			}catch(Exception ex){
				connection = null;
				preparedStatement = null;
				resultSet = null;
				
				ex.printStackTrace();
			}		
    	return discussionModel;
    }
}